/**
 * 查看装备基础数据统计信息
 */

const mysql = require('mysql2/promise');

const dbConf = {
  host: '43.143.253.188',
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

async function showStats () {
  const conn = await mysql.createConnection(dbConf);
  try {
    console.log('📊 装备基础数据统计信息:');

    // 总体统计
    const [total] = await conn.execute('SELECT COUNT(*) as total FROM equipment_base');
    console.log(`\n总计: ${total[0].total} 条装备记录`);

    // 按类型和基础类型分组统计
    const [stats] = await conn.execute(`
            SELECT 
                type_name,
                base_name,
                COUNT(*) as count,
                MIN(level) as min_level,
                MAX(level) as max_level,
                ROUND(AVG(price)) as avg_price
            FROM equipment_base 
            GROUP BY type, base, type_name, base_name
            ORDER BY type, base
        `);

    console.log('\n按类型和基础类型分组:');
    console.table(stats);

    // 按装备类型统计
    const [typeStats] = await conn.execute(`
            SELECT 
                type_name,
                COUNT(*) as count,
                MIN(level) as min_level,
                MAX(level) as max_level,
                ROUND(AVG(price)) as avg_price
            FROM equipment_base 
            GROUP BY type, type_name
            ORDER BY type
        `);

    console.log('\n按装备类型统计:');
    console.table(typeStats);

    // 按等级范围统计
    const [levelStats] = await conn.execute(`
            SELECT 
                CASE 
                    WHEN level <= 3 THEN '1-3级'
                    WHEN level <= 6 THEN '4-6级'
                    WHEN level <= 9 THEN '7-9级'
                    ELSE '10级'
                END as level_range,
                COUNT(*) as count,
                ROUND(AVG(price)) as avg_price
            FROM equipment_base 
            GROUP BY 
                CASE 
                    WHEN level <= 3 THEN '1-3级'
                    WHEN level <= 6 THEN '4-6级'
                    WHEN level <= 9 THEN '7-9级'
                    ELSE '10级'
                END
            ORDER BY MIN(level)
        `);

    console.log('\n按等级范围统计:');
    console.table(levelStats);

  } finally {
    await conn.end();
  }
}

if (require.main === module) {
  showStats().catch((err) => {
    console.error('查询失败:', err.message);
    process.exit(1);
  });
}

module.exports = { showStats };

